IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MD3.f_GetIdxFileGroupOrPartition'))
  DROP FUNCTION MD3.f_GetIdxFileGroupOrPartition
GO

--returns either the filegroup or partition scheme/column for a given index

CREATE FUNCTION MD3.f_GetIdxFileGroupOrPartition
(
  @SchemaName  SYSNAME,
  @TblName     SYSNAME,
  @IdxName     SYSNAME
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE
      @DataSpace VARCHAR(MAX),
      @FullName SYSNAME

  SELECT
      @FullName = @SchemaName + '.' + @TblName

  SELECT @DataSpace = CASE sds.type 
                         WHEN 'FG' THEN sds.name
                         WHEN 'PS' THEN sds.name + '(' + sc.name + ')'
                         ELSE NULL
                      END 
    FROM sys.tables st 
    JOIN sys.indexes si
      ON st.object_id = si.object_id
    JOIN sys.index_columns sic
      ON st.object_id = sic.object_id
     AND si.index_id = sic.index_id 
    JOIN sys.data_spaces sds
      ON si.data_space_id = sds.data_space_id
    JOIN sys.columns sc
      ON sc.object_id = sic.object_id and sc.column_id = sic.column_id   
   WHERE st.object_id = OBJECT_ID(@FullName)
     AND si.name = @IdxName
     AND (    (sds.type = 'FG' AND sic.key_ordinal = 1)
           OR (sds.type = 'PS' AND sic.partition_ordinal = 1)
         )

  RETURN @DataSpace
END
GO

